package data.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import crawler.VideoItem;
/**
 * 
 * @author ladjzero
 *
 */
public class DatabaseConnection {
	private Connection conn;
	private Statement stmt;

	
	public DatabaseConnection(String databaseUrl, String databaseUser, String databasePasswd){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://"+databaseUrl, databaseUser, databasePasswd);
			stmt = conn.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void destroy(){
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.destroy();
	}
	
	public ResultSet executeQuery(String query){
		try {
			return stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<VideoRecord> getListByExecuteQuery(String query){
		ArrayList<VideoRecord> list = new ArrayList<VideoRecord>();
		try {
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()){
				VideoRecord vr = new VideoRecord(rs.getString("id"), rs.getString("url"), rs.getString("title"));
				list.add(vr);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public void updataOneRecord(String query){
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void batchInsert(ArrayList<VideoItem> videoList, String tableName){
		try {
			Statement stmt = conn.createStatement();
			for(int i=0; i<videoList.size(); i++)
				stmt.addBatch( "INSERT IGNORE INTO "+tableName+" (url, title) VALUES (\""+ videoList.get(i).url +"\", \""+ videoList.get(i).title+"\")");
			conn.setAutoCommit(false);
			stmt.executeBatch();
			conn.commit();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
